﻿-- DELETE OBJECTS FOR A GIVEN TABLE
CREATE PROCEDURE [todoc].[sspDeleteObjectsForTable]
(
@TableSchema SYSNAME,
@TableName SYSNAME
)
--WITH EXECUTE AS 'todocuser'
AS
/*
DELETES ALL TODOC OBJECTS FOR A GIVEN TABLE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SET NOCOUNT ON;

DECLARE @HasView NVARCHAR(5) = 'False';
DECLARE @HasShadowTable NVARCHAR(5) = 'False';
DECLARE @HasDeleteProc NVARCHAR(5) = 'False';
DECLARE @HasInsertProc NVARCHAR(5) = 'False';
DECLARE @HasUpdateProc NVARCHAR(5) = 'False';
DECLARE @HasSaveProc NVARCHAR(5) = 'False';

BEGIN TRANSACTION;

BEGIN TRY

	-- see what deletes we to do on this table
	SELECT	@HasView = MAX((CASE WHEN ep.[name] = 'Todoc: Has View' THEN CONVERT(NVARCHAR(5), ep.[value]) END)),
			@HasShadowTable = MAX((CASE WHEN ep.[name] = 'Todoc: Has Shadow Table' THEN CONVERT(NVARCHAR(5), ep.[value]) END)),
			@HasDeleteProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Delete Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) END)),
			@HasInsertProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Insert Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) END)),
			@HasUpdateProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Update Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) END)),
			@HasSaveProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Save Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID(CONVERT(nvarchar(128), @TableSchema) + '.' + CONVERT(nvarchar(128), @TableName))
		AND ep.[name] LIKE 'Todoc: Has%';

	-- call the relevant delete functions
	IF (@HasView = 'True')
	BEGIN
		EXEC [todoc].[sspDropView] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	IF (@HasShadowTable = 'True')
	BEGIN
		EXEC [todoc].[sspDropShadowTable] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	IF (@HasDeleteProc = 'True')
	BEGIN
		EXEC [todoc].[sspDropDeleteSP] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	IF (@HasInsertProc = 'True')
	BEGIN
		EXEC [todoc].[sspDropInsertSP] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	IF (@HasUpdateProc = 'True')
	BEGIN
		EXEC [todoc].[sspDropUpdateSP] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	IF (@HasSaveProc = 'True')
	BEGIN
		EXEC [todoc].[sspDropSaveSP] 
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
	END

	COMMIT TRANSACTION;

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	ROLLBACK TRANSACTION;

	RETURN -1;

END CATCH;